package com.epam.facebook_client.content.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

import com.epam.facebook_client.api.API;
import com.epam.facebook_client.content.contract.AppContract;
import com.epam.facebook_client.wrapper.Albums;
import com.epam.facebook_client.wrapper.Feed;
import com.epam.facebook_client.wrapper.Photos;
import com.epam.facebook_client.wrapper.User;

public class DbOpenHelper extends SQLiteOpenHelper {

	public static final int DB_VERSION = 9;
	private static final String DB_NAME = "facebook.data.db";
	public static final String FRIENDS_TABLE = "FRIENDS";
	public static final String FEEDS_TABLE = "FEEDS";
	public static final String ALBUMS_TABLE = "ALBUMS";
	public static final String PHOTOS_TABLE = "PHOTOS";

	// friends---------------------
	public static final String[] FRIENDS_COLUMNS = { AppContract.FriendsColumns._ID,
			AppContract.FriendsColumns.NAME, AppContract.FriendsColumns.GENDER,
			AppContract.FriendsColumns.BIRTHDAY, AppContract.FriendsColumns.BIO,
			AppContract.FriendsColumns.PICTURE };

	public static final int FRIEND_ID_COL_INDEX = 0;
	public static final int FRIEND_NAME_COL_INDEX = 1;
	public static final int FRIEND_GENDER_COL_INDEX = 2;
	public static final int FRIEND_BIRTHDAY_COL_INDEX = 3;
	public static final int FRIEND_BIO_COL_INDEX = 4;
	public static final int FRIEND_PICTURE_COL_INDEX = 5;

	public static final String CREATE_FRIENDS_TABLE = "CREATE TABLE " + FRIENDS_TABLE + " ("
			+ FRIENDS_COLUMNS[FRIEND_ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ FRIENDS_COLUMNS[FRIEND_NAME_COL_INDEX] + " VARCHAR NOT NULL,"
			+ FRIENDS_COLUMNS[FRIEND_GENDER_COL_INDEX] + " VARCHAR, "
			+ FRIENDS_COLUMNS[FRIEND_BIRTHDAY_COL_INDEX] + " VARCHAR, "
			+ FRIENDS_COLUMNS[FRIEND_BIO_COL_INDEX] + " VARCHAR, "
			+ FRIENDS_COLUMNS[FRIEND_PICTURE_COL_INDEX] + " VARCHAR " + ")";
	public static final String DROP_FRIENDS_TABLE = "DROP TABLE IF EXISTS " + FRIENDS_TABLE;

	// feeds--------------------------
	public static final String[] FEEDS_COLUMNS = { AppContract.FeedColumns.FEED_ID,
			AppContract.FeedColumns.NAME, AppContract.FeedColumns.STORY, AppContract.FeedColumns.PICTURE,
			AppContract.FeedColumns.DESCRIPTION, AppContract.FeedColumns.MESSAGE,
			AppContract.FeedColumns.TYPE, AppContract.FeedColumns.LINK, AppContract.FeedColumns.CREATE_TIME,
			AppContract.FeedColumns._ID };

	public static final int FEEDS_ID_COL_INDEX = 0;
	public static final int FEEDS_NAME_COL_INDEX = 1;
	public static final int FEEDS_STORY_COL_INDEX = 2;
	public static final int FEEDS_PICTURE_COL_INDEX = 3;
	public static final int FEEDS_DESCRIPTION_COL_INDEX = 4;
	public static final int FEEDS_MESSAGE_COL_INDEX = 5;
	public static final int FEEDS_TYPE_COL_INDEX = 6;
	public static final int FEEDS_LINK_COL_INDEX = 7;
	public static final int FEEDS_CREATE_COL_INDEX = 8;
	public static final int FEEDS_ID_INDEX = 9;

	public static final String CREATE_FEEDS_TABLE = "CREATE TABLE " + FEEDS_TABLE + " ("
			+ FEEDS_COLUMNS[FEEDS_ID_COL_INDEX] + " VARCHAR NOT NULL, " + FEEDS_COLUMNS[FEEDS_NAME_COL_INDEX]
			+ " VARCHAR NOT NULL," + FEEDS_COLUMNS[FEEDS_STORY_COL_INDEX] + " VARCHAR, "
			+ FEEDS_COLUMNS[FEEDS_PICTURE_COL_INDEX] + " VARCHAR, "
			+ FEEDS_COLUMNS[FEEDS_DESCRIPTION_COL_INDEX] + " VARCHAR, "
			+ FEEDS_COLUMNS[FEEDS_MESSAGE_COL_INDEX] + " VARCHAR, " + FEEDS_COLUMNS[FEEDS_TYPE_COL_INDEX]
			+ " VARCHAR, " + FEEDS_COLUMNS[FEEDS_LINK_COL_INDEX] + " VARCHAR, "
			+ FEEDS_COLUMNS[FEEDS_CREATE_COL_INDEX] + " VARCHAR, " + FEEDS_COLUMNS[FEEDS_ID_INDEX]
			+ " INTEGER PRIMARY KEY AUTOINCREMENT " + ")";

	public static final String DROP_FEEDS_TABLE = "DROP TABLE IF EXISTS " + FEEDS_TABLE;

	// albums--------------------------
	public static final String[] ALBUMS_COLUMNS = { AppContract.AlbumColumns._ID,
			AppContract.AlbumColumns.NAME, AppContract.AlbumColumns.COVER, AppContract.AlbumColumns.COUNT,
			AppContract.AlbumColumns.USER_ID };

	public static final int ALBUMS_ID_COL_INDEX = 0;
	public static final int ALBUMS_NAME_COL_INDEX = 1;
	public static final int ALBUMS_COVER_COL_INDEX = 2;
	public static final int ALBUMS_COUNT_COL_INDEX = 3;
	public static final int ALBUMS_USER_ID_COL_INDEX = 4;

	public static final String CREATE_ALBUMS_TABLE = "CREATE TABLE " + ALBUMS_TABLE + " ("
			+ ALBUMS_COLUMNS[ALBUMS_ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ ALBUMS_COLUMNS[ALBUMS_NAME_COL_INDEX] + " VARCHAR NOT NULL,"
			+ ALBUMS_COLUMNS[ALBUMS_COVER_COL_INDEX] + " VARCHAR, "
			+ ALBUMS_COLUMNS[ALBUMS_USER_ID_COL_INDEX] + " VARCHAR NOT NULL, "
			+ ALBUMS_COLUMNS[ALBUMS_COUNT_COL_INDEX] + " INTEGER )";

	public static final String DROP_ALBUMS_TABLE = "DROP TABLE IF EXISTS " + ALBUMS_TABLE;
	
	
	// photos--------------------------
	public static final String[] PHOTOS_COLUMNS = { AppContract.PhotosColumns._ID,
		AppContract.PhotosColumns.PICTURE, AppContract.PhotosColumns.SOURCE, AppContract.PhotosColumns.TIME,
		AppContract.PhotosColumns.ALBUM_ID };
	
	public static final int PHOTOS_ID_COL_INDEX = 0;
	public static final int PHOTOS_PICTURE_COL_INDEX = 1;
	public static final int PHOTOS_SOURCE_COL_INDEX = 2;
	public static final int PHOTOS_TIME_COL_INDEX = 3;
	public static final int PHOTOS_ALBUM_ID_COL_INDEX = 4;
	
	public static final String CREATE_PHOTOS_TABLE = "CREATE TABLE " + PHOTOS_TABLE + " ("
			+ PHOTOS_COLUMNS[PHOTOS_ID_COL_INDEX] + " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ PHOTOS_COLUMNS[PHOTOS_PICTURE_COL_INDEX] + " VARCHAR,"
			+ PHOTOS_COLUMNS[PHOTOS_SOURCE_COL_INDEX] + " VARCHAR, "
			+ PHOTOS_COLUMNS[PHOTOS_TIME_COL_INDEX] + " VARCHAR, "
			+ PHOTOS_COLUMNS[PHOTOS_ALBUM_ID_COL_INDEX] + " VARCHAR )";
	
	public static final String DROP_PHOTOS_TABLE = "DROP TABLE IF EXISTS " + PHOTOS_TABLE;

	public DbOpenHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, DB_NAME, factory, DB_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		try {
			db.beginTransaction();
			db.execSQL(CREATE_FRIENDS_TABLE);
			db.execSQL(CREATE_FEEDS_TABLE);
			db.execSQL(CREATE_ALBUMS_TABLE);
			db.execSQL(CREATE_PHOTOS_TABLE);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		try {
			db.beginTransaction();
			db.execSQL(DROP_FRIENDS_TABLE);
			db.execSQL(DROP_FEEDS_TABLE);
			db.execSQL(DROP_ALBUMS_TABLE);
			db.execSQL(DROP_PHOTOS_TABLE);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		onCreate(db);
	}

	public long insertItem(String table, String nullColumnHack, ContentValues value) {
		SQLiteDatabase db = getWritableDatabase();
		long id = 0;
		try {
			db.beginTransaction();
			if (table.equals(FRIENDS_TABLE)) {
				id = db.insert(FRIENDS_TABLE, nullColumnHack,
						getContentValuesFriends(new User(value.get("data").toString())));
			}
			if (table.equals(FEEDS_TABLE)) {
				id = db.insert(FEEDS_TABLE, nullColumnHack, getContentValuesFeeds(new Feed(value.get("data")
						.toString())));
			}
			if (table.equals(ALBUMS_TABLE)) {
				id = db.insert(ALBUMS_TABLE, nullColumnHack,
						getContentValuesAlbums(new Albums(value.get("data").toString())));
			}
			if (table.equals(PHOTOS_TABLE)) {
				id = db.insert(PHOTOS_TABLE, nullColumnHack,
						getContentValuesPhotos(new Photos(value.get("data").toString()), value.get("id").toString()));
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return id;
	}



	public List<Long> insertItems(String table, String nullColumnHack, ContentValues[] values) {
		SQLiteDatabase db = getWritableDatabase();
		List<Long> listId = new ArrayList<Long>();
		try {
			db.beginTransaction();
			if (table.equals(FRIENDS_TABLE)) {
				for (ContentValues value : values) {
					User user = new User(value.get("data").toString());
					String[] id = { user.getId() };
					int update = db.update(FRIENDS_TABLE, getContentValuesFriends(user),
							AppContract.FriendsColumns._ID + " = ?", id);
					if (update == 0) {
						listId.add(db.insert(FRIENDS_TABLE, nullColumnHack, getContentValuesFriends(user)));
					}
				}
			}
			if (table.equals(FEEDS_TABLE)) {
				for (ContentValues value : values) {
					Feed feed = new Feed(value.get("data").toString());
					String[] id = { feed.getId() };
					int update = db.update(FEEDS_TABLE, getContentValuesFeeds(feed),
							AppContract.FeedColumns.FEED_ID + " = ?", id);
					if (update == 0) {
						listId.add(db.insert(FEEDS_TABLE, nullColumnHack, getContentValuesFeeds(feed)));
					}
				}
			}
			if (table.equals(ALBUMS_TABLE)) {
				for (ContentValues value : values) {
					Albums album = new Albums(value.get("data").toString());
					String[] id = { album.getId() };
					int update = db.update(ALBUMS_TABLE, getContentValuesAlbums(album),
							AppContract.AlbumColumns._ID + " = ?", id);
					if (update == 0) {
						listId.add(db.insert(ALBUMS_TABLE, nullColumnHack, getContentValuesAlbums(album)));
					}
				}
			}
			if (table.equals(PHOTOS_TABLE)) {
				for (ContentValues value : values) {
					Photos photos = new Photos(value.get("data").toString());
					String[] id = { photos.getId() };
					int update = db.update(PHOTOS_TABLE, getContentValuesPhotos(photos, value.get("id").toString()),
							AppContract.PhotosColumns._ID + " = ?", id);
					if (update == 0) {
						listId.add(db.insert(PHOTOS_TABLE, nullColumnHack, getContentValuesPhotos(photos, value.get("id").toString())));
					}
				}
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return listId;
	}

	public Cursor getItems(String table, String[] projection, String selection, String[] selectionArgs,
			String sortOrder) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			db.beginTransaction();
			c = db.query(table, projection, selection, selectionArgs, null, null, sortOrder);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return c;
	}

	public int deleteItem(String table, String whereClause, String[] whereArgs) {
		SQLiteDatabase db = getWritableDatabase();
		int delete = 0;
		try {
			db.beginTransaction();
			delete = db.delete(table, whereClause, whereArgs);
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return delete;
	}

	public int updateItem(String table, ContentValues values, String whereClause, String[] whereArgs) {
		SQLiteDatabase db = getWritableDatabase();
		int update = 0;
		try {
			db.beginTransaction();
			if (table.equals(FRIENDS_TABLE)) {
				update = db.update(table, getContentValuesFriends(new User(values.get("data").toString())),
						whereClause, whereArgs);
			}
			if (table.equals(FEEDS_TABLE)) {
				update = db.update(table, getContentValuesFeeds(new Feed(values.get("data").toString())),
						whereClause, whereArgs);
			}
			if (table.equals(ALBUMS_TABLE)) {
				update = db.update(table, getContentValuesAlbums(new Albums(values.get("data").toString())),
						whereClause, whereArgs);
			}
			if (table.equals(PHOTOS_TABLE)) {
				update = db.update(table, getContentValuesPhotos(new Photos(values.get("data").toString()), values.get("id").toString()),
						whereClause, whereArgs);
			}
			db.setTransactionSuccessful();
		} finally {
			db.endTransaction();
		}
		return update;
	}

	public ContentValues getContentValuesFriends(User user) {
		ContentValues values = new ContentValues();
		values.put(FRIENDS_COLUMNS[FRIEND_ID_COL_INDEX], user.getId());
		values.put(FRIENDS_COLUMNS[FRIEND_NAME_COL_INDEX], user.getName());
		if (user.getGender() != null && !user.getGender().equals(""))
			values.put(FRIENDS_COLUMNS[FRIEND_GENDER_COL_INDEX], user.getGender());
		if (user.getBirthday() != null && !user.getBirthday().equals(""))
			values.put(FRIENDS_COLUMNS[FRIEND_BIRTHDAY_COL_INDEX], user.getBirthday());
		if (user.getBio() != null && !user.getBio().equals(""))
			values.put(FRIENDS_COLUMNS[FRIEND_BIO_COL_INDEX], user.getBio());
		values.put(FRIENDS_COLUMNS[FRIEND_PICTURE_COL_INDEX], API.getPictureSquare(user.getId()));
		return values;
	}

	private ContentValues getContentValuesFeeds(Feed feed) {
		ContentValues values = new ContentValues();
		values.put(FEEDS_COLUMNS[FRIEND_ID_COL_INDEX], feed.getId());
		values.put(FEEDS_COLUMNS[FRIEND_NAME_COL_INDEX], feed.getName());
		if (feed.getStory() != null && !feed.getStory().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_STORY_COL_INDEX], feed.getStory());
		if (feed.getType() != null && !feed.getType().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_TYPE_COL_INDEX], feed.getType());
		if (feed.getDescription() != null && !feed.getDescription().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_DESCRIPTION_COL_INDEX], feed.getDescription());
		if (feed.getMessage() != null && !feed.getMessage().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_MESSAGE_COL_INDEX], feed.getMessage());
		if (feed.getPicture() != null && !feed.getPicture().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_PICTURE_COL_INDEX], feed.getPicture());
		if (feed.getLink() != null && !feed.getLink().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_LINK_COL_INDEX], feed.getLink());
		if (feed.getCreateTime() != null && !feed.getCreateTime().equals(""))
			values.put(FEEDS_COLUMNS[FEEDS_CREATE_COL_INDEX], feed.getCreateTime());
		return values;
	}

	private ContentValues getContentValuesAlbums(Albums albums) {
		ContentValues values = new ContentValues();
		values.put(ALBUMS_COLUMNS[ALBUMS_ID_COL_INDEX], albums.getId());
		values.put(ALBUMS_COLUMNS[ALBUMS_NAME_COL_INDEX], albums.getName());
		values.put(ALBUMS_COLUMNS[ALBUMS_COUNT_COL_INDEX], albums.getCount());
		values.put(ALBUMS_COLUMNS[ALBUMS_USER_ID_COL_INDEX], albums.getUserId());
		if (albums.getCover() != null && !albums.getCover().equals("")) {
			values.put(ALBUMS_COLUMNS[ALBUMS_COVER_COL_INDEX], API.getCover(albums.getCover()));
		} else {
			values.put(ALBUMS_COLUMNS[ALBUMS_COVER_COL_INDEX], "");
		}

		return values;
	}
	
	private ContentValues getContentValuesPhotos(Photos photos, String id_album) {
		ContentValues values = new ContentValues();
		values.put(PHOTOS_COLUMNS[PHOTOS_ID_COL_INDEX], photos.getId());
		values.put(PHOTOS_COLUMNS[PHOTOS_PICTURE_COL_INDEX], photos.getPicture());
		values.put(PHOTOS_COLUMNS[PHOTOS_SOURCE_COL_INDEX], photos.getSource());
		values.put(PHOTOS_COLUMNS[PHOTOS_TIME_COL_INDEX], photos.getTime());
		values.put(PHOTOS_COLUMNS[PHOTOS_ALBUM_ID_COL_INDEX], id_album);
		return values;
	}
}
